package biz.bbtec.sbm.issuetracking.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import com.opensymphony.xwork2.ActionContext;

import biz.bbtec.sbm.db.dbConnection;
import biz.bbtec.sbm.issuetracking.bean.AttachmentBean;
import biz.bbtec.sbm.issuetracking.bean.TaskBean;
import biz.bbtec.sbm.issuetracking.bean.WorkBean;
import biz.bbtec.sbm.user.bean.UserBean;
import biz.bbtec.sbm.email.action.EmailAlarmAction;
import biz.bbtec.sbm.email.service.EmailAlarmService;
import biz.bbtec.sbm.email.bean.MailSenderInfo;
import biz.bbtec.sbm.email.factory.EmailAlarmFactory;
public class WorkPackageService {
	dbConnection db = new dbConnection();

	public boolean ModifyWork(WorkBean work, UserBean user, String filename) {
		boolean flag = false;

		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		Date date = new Date();
		String thisDate = sdf.format(date);

		String sql = "update workpackage set title=? , description=? , progress=?, type=?, status=?, priority=?, startdate=?, deadline=?, workamount=? where wpid= "
				+ "'" + work.getWpid() + "'";

		Connection conn = db.getConnection();
		PreparedStatement past = null;
		try {
			past = conn.prepareStatement(sql);
			past.setString(1, work.getTitle());
			past.setString(2, work.getDescription());
			past.setInt(3, work.getProgress());
			past.setInt(4, work.getType());
			past.setInt(5, work.getStatus());
			past.setInt(6, work.getPriority());
			past.setString(7, work.getStartdate());
			past.setString(8, work.getDeadline());
			past.setDouble(9, work.getWorkamount());
			past.executeUpdate();
			past.close();
			if (user.getUserid() != -1) {
				String sql1 = "update workpackageallocation set userid= ?  where wpid= "
						+ "'" + work.getWpid() + "'";
				past = conn.prepareStatement(sql1);
				past.setInt(1, user.getUserid());
				past.executeUpdate();
				past.close();
			}

			if (filename != null) {
				String sql3 = "insert into attachment(wpid,title,date,taskid) values ("
						+ "'"
						+ work.getWpid()
						+ "'"
						+ ","
						+ "'"
						+ filename
						+ "'"
						+ ","
						+ "'"
						+ thisDate
						+ "'"
						+ ","
						+ "'"
						+ work.getTaskid() + "'" + ")";
				past = conn.prepareStatement(sql3);
				past.executeUpdate(sql3);
				past.close();
			}
			flag = true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return flag;
	}

	public List ShowAllWork(TaskBean taskbean, int pagenow, int pagesize) {
		List<WorkBean> list = new ArrayList();
		int taskid = 0;
		Map session = ActionContext.getContext().getSession();
		if (taskbean == null) {
			taskid = (Integer) session.get("worktaskid");
			taskbean = new TaskBean();
			taskbean.setTaskid(taskid);
			if (pagenow != 0) {
				session.remove("worktaskid");
			}

		} else {
			taskid = taskbean.getTaskid();
		}
		UserBean user = (UserBean) session.get("user");
		String sql = "select * from workpackageallocation,workpackage,user "
				+ "where workpackage.wpid = workpackageallocation.wpid and "
				+ "workpackageallocation.userid = user.userid and taskid = "
				+ "'" + taskid + "'" + " order by "
				+ "workpackage.status desc,workpackage.priority asc,"
				+ "workpackage.progress asc,workpackage.wpid desc limit ?,? ";

		Connection conn = db.getConnection();
		try {
			PreparedStatement past = conn.prepareStatement(sql);
			past.setInt(1, pagenow * pagesize);
			past.setInt(2, pagesize);
			ResultSet rs = past.executeQuery();
			while (rs.next()) {
				WorkBean work = new WorkBean();
				work.setTaskid(taskbean.getTaskid());
				
				String content = rs.getString("description");
				content = content.replace(" ", "&nbsp;");
				content = content.replace("\n", "<br />");
				work.setDescription(content);
				
				work.setProgress(Integer.parseInt(rs.getString("progress")));
				work.setStatus(Integer.parseInt(rs.getString("status")));
				work.setWpid(Integer.parseInt(rs.getString("wpid")));
				int wpid = work.getWpid();
				String sql2 = "select count(*) from note where wpid =" + "'"
						+ wpid + "'" + "group by wpid";
				String sql3 = "select past from workaccess where wpid = " + "'"
						+ wpid + "'" + " and userid = " + "'"
						+ user.getUserid() + "'";
				try {
					Statement stmt = conn.createStatement();
					ResultSet rs2 = stmt.executeQuery(sql2);
					if (rs2.next()) {
						int noteno = rs2.getInt(1);
						work.setNoteno(noteno);
					}

				} catch (SQLException e) {
					e.printStackTrace();
				}
				work.setTitle(rs.getString("title"));
				work.setType(Integer.parseInt(rs.getString("type")));
				work.setName(rs.getString("name"));
				work.setPriority(Integer.parseInt(rs.getString("priority")));
				work.setStartdate(rs.getString("startdate"));
				work.setDeadline(rs.getString("deadline"));
				if (rs.getString("workamount") == null) {
					work.setWorkamount(0.0);
				} else {
					work.setWorkamount(Double.parseDouble(rs
							.getString("workamount")));
				}
				try {
					PreparedStatement past3 = conn.prepareStatement(sql3);
					ResultSet rs3 = past3.executeQuery();
					if (rs3.next()) {
						work.setPast(Integer.parseInt(rs3.getString("past")));
					} else {
						work.setPast(0);
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
				list.add(work);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return list;
	}

	public int CountAllWork(TaskBean taskbean) {
		int total = 0;
		int taskid = 0;
		Map session = ActionContext.getContext().getSession();
		if (taskbean == null) {
			taskid = (Integer) session.get("worktaskid");
			taskbean = new TaskBean();
			taskbean.setTaskid(taskid);
			session.remove("worktaskid");
		} else {
			taskid = taskbean.getTaskid();
		}
		String sql1 = "select count(*) from workpackage where taskid =" + "'"
				+ taskbean.getTaskid() + "'";
		Connection conn = db.getConnection();
		PreparedStatement past = null;
		ResultSet rs = null;

		try {
			past = conn.prepareStatement(sql1);
			rs = past.executeQuery();
			if (rs.next()) {
				total = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}

		return total;
	}

	public List ShowUndoWork(TaskBean taskbean) {
		List<WorkBean> list = new ArrayList();
		int taskid = 0;
		Map session = ActionContext.getContext().getSession();
		if (taskbean == null) {
			taskid = (Integer) session.get("worktaskid");
			taskbean = new TaskBean();
			taskbean.setTaskid(taskid);
			session.remove("worktaskid");
		} else {
			taskid = taskbean.getTaskid();
		}
		UserBean user = (UserBean) session.get("user");
		String sql = "select * from workpackageallocation,workpackage,user "
				+ "where workpackage.wpid = workpackageallocation.wpid and "
				+ "workpackageallocation.userid = user.userid and taskid = "
				+ "'"
				+ taskid
				+ "'"
				+ "and workpackage.progress < 100 "
				+ "order by workpackage.status desc,workpackage.priority asc,workpackage.progress desc";

		Connection conn = db.getConnection();
		try {
			PreparedStatement past = conn.prepareStatement(sql);
			ResultSet rs = past.executeQuery();
			while (rs.next()) {
				WorkBean work = new WorkBean();
				work.setTaskid(taskbean.getTaskid());
				work.setDescription(rs.getString("description"));
				work.setProgress(Integer.parseInt(rs.getString("progress")));
				work.setStatus(Integer.parseInt(rs.getString("status")));
				work.setWpid(Integer.parseInt(rs.getString("wpid")));
				int wpid = work.getWpid();
				String sql2 = "select count(*) from note where wpid =" + "'"
						+ wpid + "'" + "group by wpid";
				String sql3 = "select past from workaccess where wpid = " + "'"
						+ wpid + "'" + " and userid = " + "'"
						+ user.getUserid() + "'";
				try {
					Statement stmt = conn.createStatement();
					ResultSet rs2 = stmt.executeQuery(sql2);
					if (rs2.next()) {
						int noteno = rs2.getInt(1);
						work.setNoteno(noteno);
					}

				} catch (SQLException e) {
					e.printStackTrace();
				}
				work.setTitle(rs.getString("title"));
				work.setType(Integer.parseInt(rs.getString("type")));
				work.setName(rs.getString("name"));
				work.setPriority(Integer.parseInt(rs.getString("priority")));
				work.setStartdate(rs.getString("startdate"));
				work.setDeadline(rs.getString("deadline"));
				if (rs.getString("workamount") == null) {
					work.setWorkamount(0.0);
				} else {
					work.setWorkamount(Double.parseDouble(rs
							.getString("workamount")));
				}
				try {
					PreparedStatement past3 = conn.prepareStatement(sql3);
					ResultSet rs3 = past3.executeQuery();
					if (rs3.next()) {
						work.setPast(Integer.parseInt(rs3.getString("past")));
					} else {
						work.setPast(0);
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
				list.add(work);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return list;
	}

	public List ShowActiveWork(TaskBean taskbean) {
		List<WorkBean> list = new ArrayList();
		int taskid = 0;
		Map session = ActionContext.getContext().getSession();
		if (taskbean == null) {
			taskid = (Integer) session.get("worktaskid");
			taskbean = new TaskBean();
			taskbean.setTaskid(taskid);
			session.remove("worktaskid");
		} else {
			taskid = taskbean.getTaskid();
		}
		UserBean user = (UserBean) session.get("user");
		String sql = "select * from workpackageallocation,workpackage,user "
				+ "where workpackage.status=1 and workpackage.wpid = workpackageallocation.wpid and "
				+ "workpackageallocation.userid = user.userid and taskid = "
				+ "'"
				+ taskid
				+ "'"
				+ "order by workpackage.status desc,workpackage.priority asc,workpackage.progress desc";

		Connection conn = db.getConnection();
		try {
			PreparedStatement past = conn.prepareStatement(sql);
			ResultSet rs = past.executeQuery();
			while (rs.next()) {
				WorkBean work = new WorkBean();
				work.setTaskid(taskbean.getTaskid());
				work.setDescription(rs.getString("description"));
				work.setProgress(Integer.parseInt(rs.getString("progress")));
				work.setStatus(Integer.parseInt(rs.getString("status")));
				work.setWpid(Integer.parseInt(rs.getString("wpid")));
				int wpid = work.getWpid();
				String sql2 = "select count(*) from note where wpid =" + "'"
						+ wpid + "'" + "group by wpid";
				String sql3 = "select past from workaccess where wpid = " + "'"
						+ wpid + "'" + " and userid = " + "'"
						+ user.getUserid() + "'";
				try {
					Statement stmt = conn.createStatement();
					ResultSet rs2 = stmt.executeQuery(sql2);
					if (rs2.next()) {
						int noteno = rs2.getInt(1);
						work.setNoteno(noteno);
					}

				} catch (SQLException e) {
					e.printStackTrace();
				}
				work.setTitle(rs.getString("title"));
				work.setType(Integer.parseInt(rs.getString("type")));
				work.setName(rs.getString("name"));
				work.setPriority(Integer.parseInt(rs.getString("priority")));
				work.setStartdate(rs.getString("startdate"));
				work.setDeadline(rs.getString("deadline"));
				if (rs.getString("workamount") == null) {
					work.setWorkamount(0.0);
				} else {
					work.setWorkamount(Double.parseDouble(rs
							.getString("workamount")));
				}
				try {
					PreparedStatement past3 = conn.prepareStatement(sql3);
					ResultSet rs3 = past3.executeQuery();
					if (rs3.next()) {
						work.setPast(Integer.parseInt(rs3.getString("past")));
					} else {
						work.setPast(0);
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
				list.add(work);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return list;
	}
   public Boolean AlertWork(WorkBean workBean)throws SQLException{
	   MailSenderInfo msi=new MailSenderInfo();
	   msi.setMailServerHost("smtp.exmail.qq.com");
		msi.setMailServerPort("25");
		msi.setValidate(true);
		msi.setUserName("sbm-service@bbtec.biz");
		msi.setPassword("bb123456");// 您的邮箱密码
		msi.setFromAddress("sbm-service@bbtec.biz");
		EmailAlarmService ea = EmailAlarmFactory.getInstance()
				.createEmailAlarmService();
		String work=workBean.getTitle();
		StringBuffer message = new StringBuffer("From STM小团队辅助系统<br/>您好<br/>&nbsq;&nbsq;&nbsq;&nbsq;您的工作包( <font color='red'>  "+work+" </font>  )即将到期,请尽快完成并登陆STM系统 http://stm.bbtec.biz更新进度");
	   String email=null;
	   Boolean flag=false;
	   String sql="select email from user where userid=?";
	   Connection conn=db.getConnection();
	   PreparedStatement past=null;  
	   try{	
		   past=conn.prepareStatement(sql);
		   past.setInt(1, workBean.getUserid());
		   ResultSet rs=past.executeQuery();
		   if (!rs.next()) {
				return false;
			}
		   else{
			 email=rs.getString("email");
			 msi.setToAddress(email);
			 msi.setSubject("您有工作任务已到期");
			 flag=true;
		   }
		   
	   }catch(Exception e){
		   e.printStackTrace();
		   
	   }finally{
		   db.closeConnection(conn);
	   }
	   msi.setContent(message.toString());
		ea.mailsend(msi);
	   return  flag;
   }
   
	public WorkBean ShowOneWork(WorkBean workbean) throws SQLException {
		WorkBean work = new WorkBean();
		String sql = "select * from workpackage where wpid= ?";

		Connection conn = db.getConnection();
		PreparedStatement past = null;

		Map session = ActionContext.getContext().getSession();
		UserBean user1 = (UserBean) session.get("user");
		try {
			past = conn.prepareStatement(sql);
			past.setInt(1, workbean.getWpid());
			ResultSet rs = past.executeQuery();
			if (!rs.next()) {
				return null;
			}
			work.setTitle(rs.getString("title"));
			work.setWpid(Integer.parseInt(rs.getString("wpid")));
			work.setStatus(Integer.parseInt(rs.getString("status")));
			work.setProgress(Integer.parseInt(rs.getString("progress")));
			work.setPriority(Integer.parseInt(rs.getString("priority")));
			work.setDescription(rs.getString("description"));
			work.setType(Integer.parseInt(rs.getString("type")));
			work.setStartdate(rs.getString("startdate"));
			work.setDeadline(rs.getString("deadline"));
			work.setTaskid(Integer.parseInt(rs.getString("taskid")));
			if (rs.getString("workamount") != null) {
				work.setWorkamount(Double.parseDouble(rs
						.getString("workamount")));
			} else {
				work.setWorkamount(0.0);
			}
			String sql2 = "select user.name,user.userid from user,task,workpackage,taskaccess"
					+ " where user.userid=taskaccess.userid and"
					+ " taskaccess.taskid = workpackage.taskid "
					+ " and workpackage.wpid = "
					+ "'"
					+ work.getWpid()
					+ "'"
					+ "group by user.userid";

			String sql3 = "update workaccess set past = '0' where wpid =" + "'"
					+ work.getWpid() + "'" + " and userid = " + "'"
					+ user1.getUserid() + "'";
			PreparedStatement past3 = conn.prepareStatement(sql3);
			past3.executeUpdate();
			past3.close();

			String sql4 = "select user.name,user.userid from workpackage,workpackageallocation,user where workpackage.wpid=workpackageallocation.wpid and workpackageallocation.userid=user.userid and workpackage.wpid="
					+ "'" + work.getWpid() + "'";
			past3 = conn.prepareStatement(sql4);
			rs = past3.executeQuery(sql4);
			if (rs.next()) {
				work.setName(rs.getString(1));
				work.setUserid(rs.getInt(2));
			}
			rs.close();
			past3.close();

			List<UserBean> list = new ArrayList<UserBean>();
			past = conn.prepareStatement(sql2);
			rs = past.executeQuery();
			while (rs.next()) {
				UserBean user = new UserBean();
				user.setUserid(Integer.parseInt(rs.getString("userid")));
				user.setName(rs.getString("name"));
				list.add(user);
			}
			work.setList(list);
			rs.close();
			past.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return work;
	}

	public boolean DeleteWork(WorkBean work) throws SQLException {
		boolean flag = false;

		Connection conn = db.getConnection();
		conn.setAutoCommit(false);
		PreparedStatement past = null;

		String sql1 = "delete from workpackage where wpid =" + "'"
				+ work.getWpid() + "'";
		try {
			past = conn.prepareStatement(sql1);
			past.executeUpdate(sql1);
			conn.commit();
			flag = true;
		} catch (SQLException e) {
			conn.rollback();
			e.printStackTrace();
		} finally {
			conn.setAutoCommit(true);
			conn.close();
			past.close();
			db.closeConnection(conn);
		}
		return flag;
	}

	public List<AttachmentBean> attlist(WorkBean work) {
		List<AttachmentBean> list = new ArrayList<AttachmentBean>();
		String sql1 = "select attid,title from attachment where wpid = ?";
		Connection conn = db.getConnection();
		PreparedStatement past = null;
		ResultSet rs = null;
		try {
			past = conn.prepareStatement(sql1);
			past.setInt(1, work.getWpid());
			rs = past.executeQuery();
			while (rs.next()) {
				AttachmentBean att = new AttachmentBean();
				att.setAttid(rs.getInt(1));
				att.setTitle(rs.getString(2));
				list.add(att);
			}
			rs.close();
			past.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return list;
	}

	public boolean AddWork(WorkBean workbean, String filename, UserBean user)
			throws ParseException, SQLException {
		boolean flag = false;

		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		Date date = new Date();
		String thisDate = sdf.format(date);

		Map session = ActionContext.getContext().getSession();
		int taskid = (Integer) session.get("worktaskid");
		workbean.setTaskid(taskid);
		Connection conn = db.getConnection();
		PreparedStatement past = null;
		ResultSet rs = null;
		int wpid = 0;
		try {

			String sql1 = "insert into workpackage(status,progress,priority,title,description,type,startdate,deadline,taskid,workamount) values("
					+ "'"
					+ workbean.getStatus()
					+ "'"
					+ ","
					+ "'"
					+ workbean.getProgress()
					+ "'"
					+ ","
					+ "'"
					+ workbean.getPriority()
					+ "'"
					+ ","
					+ "'"
					+ workbean.getTitle()
					+ "'"
					+ ","
					+ "'"
					+ workbean.getDescription()
					+ "'"
					+ ","
					+ "'"
					+ workbean.getType()
					+ "'"
					+ ","
					+ "'"
					+ workbean.getStartdate()
					+ "'"
					+ ","
					+ "'"
					+ workbean.getDeadline()
					+ "'"
					+ ","
					+ "'"
					+ workbean.getTaskid()
					+ "'"
					+ ","
					+ "'"
					+ workbean.getWorkamount() + "'" + ")";

			past = conn.prepareStatement(sql1, Statement.RETURN_GENERATED_KEYS);
			past.executeUpdate(sql1);
			rs = past.getGeneratedKeys();

			if (rs.next()) {
				wpid = rs.getInt(1);
				workbean.setWpid(wpid);
				workbean.setUserid(user.getUserid());
				String sql2 = "insert into workpackageallocation(wpid,userid,role) values("
						+ "'"
						+ workbean.getWpid()
						+ "'"
						+ ","
						+ "'"
						+ workbean.getUserid()
						+ "'"
						+ ","
						+ "'"
						+ workbean.getRole() + "'" + ")";
				past = conn.prepareStatement(sql2);
				past.executeUpdate(sql2);
				past.close();
				if (filename != null) {
					String sql3 = "insert into attachment(wpid,title,date,taskid) values ("
							+ "'"
							+ workbean.getWpid()
							+ "'"
							+ ","
							+ "'"
							+ filename
							+ "'"
							+ ","
							+ "'"
							+ thisDate
							+ "'"
							+ ","
							+ "'" + workbean.getTaskid() + "'" + ")";
					past = conn.prepareStatement(sql3);
					past.executeUpdate(sql3);
					past.close();
				}
				flag = true;

			} else {
				return flag;
			}
			past.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		String sql4 = "select user.userid from user,taskaccess,workpackage where taskaccess.userid = user.userid and taskaccess.taskid = workpackage.taskid and workpackage.wpid = "
				+ "'" + wpid + "'";
		try {
			past = conn.prepareStatement(sql4);
			rs = past.executeQuery();
			int judge = 1;
			while (rs.next()) {
				String sql5 = "insert into workaccess(wpid,userid,past) values ("
						+ "'"
						+ wpid
						+ "'"
						+ ","
						+ "'"
						+ Integer.parseInt(rs.getString("userid"))
						+ "'"
						+ ","
						+ "'" + judge + "'" + ")";
				PreparedStatement past2 = conn.prepareStatement(sql5);
				past2.executeUpdate(sql5);
				past2.close();
			}
			rs.close();
			past.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return flag;
	}

	public boolean DeleteAttachment(AttachmentBean att) throws SQLException {
		boolean flag = false;

		Connection conn = db.getConnection();
		conn.setAutoCommit(false);
		PreparedStatement past = null;

		String sql1 = "delete from attachment where wpid =" + "'"
				+ att.getWpid() + "'" + "and attid = " + "'" + att.getAttid()
				+ "'";
		try {

			past = conn.prepareStatement(sql1);
			past.executeUpdate(sql1);
			flag = true;
			past.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return flag;
	}

	public List ShowAllUser(TaskBean task) {
		List<UserBean> list = new ArrayList();
		String sql = "select user.name,user.userid from user,task,workpackage,taskaccess"
				+ " where user.userid=taskaccess.userid and"
				+ " taskaccess.taskid ="
				+ "'"
				+ task.getTaskid()
				+ "'"
				+ "group by user.userid";
		Connection conn = db.getConnection();
		try {
			PreparedStatement past = conn.prepareStatement(sql);
			ResultSet rs = past.executeQuery();
			while (rs.next()) {
				UserBean user = new UserBean();
				user.setUserid(Integer.parseInt(rs.getString("userid")));
				user.setName(rs.getString("name"));
				list.add(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return list;
	}

	public List<WorkBean> showmain() {
		List<WorkBean> list = new ArrayList<WorkBean>();

		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		Date date = new Date();
		String thisDate = sdf.format(date);

		Map session = ActionContext.getContext().getSession();
		UserBean user = (UserBean) session.get("user");

		String sql = "select title from workpackage,taskaccess where workpackage.taskid =taskaccess.taskid and taskaccess.userid = '"
				+ user.getUserid()
				+ "' and deadline < '"
				+ thisDate
				+ "' and progress<'100'";

		Connection conn = db.getConnection();
		PreparedStatement past = null;
		ResultSet rs = null;
		try {
			past = conn.prepareStatement(sql);
			rs = past.executeQuery();
			while (rs.next()) {
				WorkBean work = new WorkBean();
				work.setTitle(rs.getString("title"));
				list.add(work);
			}
			rs.close();
			past.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return list;
	}

}
